﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using E4RPDevExpress10;
using E4RPPrintManager10;
using E4RPTools;
using Microsoft.Office.Interop.Excel;
using MobileLoader.DataSet;

namespace MobileLoader
{
    public class Controller
    {
        private SqlConnection _connection;

        public IWin32Window OwnerWindow { get; set; }

        public string LogFile { get; set; }


        private void FixConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _connection.Open();
        }

        private TMNLojasDataSet GetTmnShops()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetShops(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler as lojas TMN.");
                return new TMNLojasDataSet();
            }
        }

        private static int GetShopOfTmn(string value, TMNLojasDataSet lojaDataSet)
        {
            foreach (TMNLojasDataSet.LojaRow shop in lojaDataSet.Loja)
                if (value.Contains(shop.SAP.ToString()))
                    return shop.SAP;
            return -1;
        }

        private static int FindNextTmnShop(Worksheet worksheet, int rowIndexStart, TMNLojasDataSet lojaDataSet)
        {
            for (int rowIndex = rowIndexStart; rowIndex < rowIndexStart + 20; rowIndex++)
            {
                string shop = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1);
                if (shop == "")
                    continue;
                if (GetShopOfTmn(shop, lojaDataSet) != -1)
                    return rowIndex;
            }
            return -1;
        }

        private int LoadTmnShopLoads(Worksheet worksheet, int rowIndex, TMNLojasDataSet lojaDataSet, TMNCarregamentoDataSet dataSet)
        {
            int shop = GetShopOfTmn(ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1), lojaDataSet);
            OnImportingMessage("A importar carregamentos TMN da loja " + lojaDataSet.Loja.FindBySAP(shop).Nome);
            rowIndex += 2;
            while (ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1) != "")
            {
                TMNCarregamentoDataSet.CarregamentoRow load = dataSet.Carregamento.NewCarregamentoRow();
                load.SAPLoja = shop;
                load.Telemovel = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1);
                load.FacturaRecibo = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 2);
                load.Valor = ExcelHelper.GetCellValueAsDouble(worksheet, rowIndex, 3);
                var dateTime = ExcelHelper.GetCellValueAsDateTime(worksheet, rowIndex, 4);
                if (dateTime != DateTime.MinValue)
                    load.DataHora = ExcelHelper.GetCellValueAsDateTime(worksheet, rowIndex, 4);
                load.Cheque = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 5);
                load.Banco = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 6);
                dataSet.Carregamento.AddCarregamentoRow(load);
                rowIndex++;
            }
            return rowIndex;
        }

        private TMNCarregamentoDataSet LoadTmnLoads(Worksheet worksheet)
        {
            OnImportingMessage("A importar carregamentos TMN");
            var dataSet = new TMNCarregamentoDataSet();
            var lojaDataSet = GetTmnShops();
            int rowIndex = FindNextTmnShop(worksheet, 1, lojaDataSet);
            while (rowIndex > 0)
            {
                rowIndex = LoadTmnShopLoads(worksheet, rowIndex, lojaDataSet, dataSet);
                rowIndex = FindNextTmnShop(worksheet, rowIndex, lojaDataSet);
            }
            OnImportingMessage("");
            return dataSet;
        }

        private TMNCarregamentoDataSet LoadTmnLoadsFromExcel(string fileName)
        {
            using (new UserInterfaceCulture())
            {
                var excelApplication = new Microsoft.Office.Interop.Excel.Application
                {
                    DisplayAlerts = false,
                    ScreenUpdating = false,
                    Visible = false,
                    UserControl = false,
                    Interactive = false,
                    DisplayInfoWindow = false,
                    DisplayStatusBar = false,
                    ShowStartupDialog = false
                };
                try
                {
                    excelApplication.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    if (excelApplication.Worksheets.Count == 0)
                        return new TMNCarregamentoDataSet();
                    var workSheet = (Worksheet)excelApplication.Worksheets[1];
                    if (workSheet == null)
                        return new TMNCarregamentoDataSet();
                    return LoadTmnLoads(workSheet);
                }
                finally
                {
                    var workbooks = excelApplication.Workbooks;
                    workbooks.Close();
                    Marshal.ReleaseComObject(workbooks);
                    excelApplication.Quit();
                    Marshal.ReleaseComObject(excelApplication);
                    GC.Collect();
                }
            }
        }

        private static bool LineValid(string line)
        {
            if(line.Length == 0)
                return false;
            string[] values = line.Split(';');
            if(values.Length == 0)
                return false;
            foreach(string value in values)
                if(value.Length != 0)
                    return true;
            return false;
        }

        private static void LoadTmnShopLoads(int shop, StreamReader reader, TMNCarregamentoDataSet dataSet)
        {
            reader.ReadLine();
            var line = reader.ReadLine();
            try
            {
                while (LineValid(line))
                {
                    string[] values = line.Split(';');
                    TMNCarregamentoDataSet.CarregamentoRow load = dataSet.Carregamento.NewCarregamentoRow();
                    load.SAPLoja = shop;
                    load.Telemovel = values.Length < 1 ? "" : values[0];
                    load.FacturaRecibo = values.Length < 2 ? "" : values[1];
                    load.Valor = values.Length < 3 ? 0 : Convert.ToDouble(values[2]);
                    if (values.Length >= 4 && !string.IsNullOrEmpty(values[3])) load.DataHora = Convert.ToDateTime(values[3]);
                    load.Cheque = values.Length < 5 ? "" : values[4];
                    load.Banco = values.Length < 6 ? "" : values[5];
                    dataSet.Carregamento.AddCarregamentoRow(load);
                    line = reader.ReadLine();
                }            
            }
            catch (Exception)
            {
                MessageBox.Show(shop + Environment.NewLine + line);
            }
            
        }

        private TMNCarregamentoDataSet LoadTmnLoadsFromCsv(string fileName)
        {
            OnImportingMessage("A importar carregamentos TMN");
            var lojaDataSet = GetTmnShops();
            var dataSet = new TMNCarregamentoDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            while (!reader.EndOfStream)
            {
                var shop = GetTmnShop(reader, lojaDataSet);
                if (shop == -2) return dataSet;
                if (shop == -1) return new TMNCarregamentoDataSet();
                OnImportingMessage("A importar carregamentos TMN da loja " + lojaDataSet.Loja.FindBySAP(shop).Nome);
                LoadTmnShopLoads(shop, reader, dataSet);
            }
            return dataSet;
        }

        public bool ExistsLoads(DateTime day)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var exists = Database.ExistsTmnLoads(sqlTransaction, day);
                sqlTransaction.Commit();
                return exists;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), 
                    string.Format("Não foi possível validar se existem carregamentos TMN no dia '{0}'.", day.ToShortDateString()));
                return true;
            }
        }

        private bool TmnLoadsValid(TMNCarregamentoDataSet dataSet)
        {
            var importedDays = new List<DateTime>();
            foreach (TMNCarregamentoDataSet.CarregamentoRow load in dataSet.Carregamento)
            {
                if(!importedDays.Contains(load.DataHora.Date))
                    importedDays.Add(load.DataHora.Date);
            }
            var daysWithLoads = "";
            foreach (var day in importedDays)
            {
                if (!ExistsLoads(day))
                    continue;
                if (daysWithLoads.Length > 0) daysWithLoads += ", ";
                daysWithLoads += day.ToShortDateString();
            }
            if (daysWithLoads.Length > 0)
                MessageBox.Show(OwnerWindow,
                                string.Format("Existem carregamentos nos dias {0}.", daysWithLoads) + Environment.NewLine +
                                "Por favor, elimine os carregamentos.", "Importar Carregamentos TMN",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            return daysWithLoads.Length == 0;
        }

        private bool SaveTmnLoads(TMNCarregamentoDataSet dataSet)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                OnImportingMessage("A actualizar a base de dados com os carregamentos TMN importados.");
                Database.InsertTmnLoads(sqlTransaction, dataSet);
                sqlTransaction.Commit();
                return true;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível gravar os carregamentos TMN.");
                return false;
            }
            finally
            {
                OnImportingMessage("");
            }
        }

        private int LoadTmnShopBills(Worksheet worksheet, int rowIndex, TMNLojasDataSet lojaDataSet, TMNFacturaDataSet dataSet)
        {
            int shop = GetShopOfTmn(ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1), lojaDataSet);
            OnImportingMessage("A importar facturas TMN da loja " + lojaDataSet.Loja.FindBySAP(shop).Nome);
            rowIndex += 3;
            while (ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1) != "")
            {
                TMNFacturaDataSet.FacturaRow bill = dataSet.Factura.NewFacturaRow();
                bill.SAPLoja = shop;
                bill.Empresa = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 1);
                bill.Factura = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 2);
                bill.Recibo = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 3);
                bill.Conta = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 4);
                bill.Valor = ExcelHelper.GetCellValueAsDouble(worksheet, rowIndex, 5);
                var dateTime = ExcelHelper.GetCellValueAsDateTime(worksheet, rowIndex, 6);
                if (dateTime != DateTime.MinValue)
                    bill.DataHora = ExcelHelper.GetCellValueAsDateTime(worksheet, rowIndex, 6);
                bill.Cheque = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 7);
                bill.Banco = ExcelHelper.GetCellValueAsString(worksheet, rowIndex, 8);
                dataSet.Factura.AddFacturaRow(bill);
                rowIndex++;
            }
            return rowIndex;
        }

        private TMNFacturaDataSet LoadTmnBills(Worksheet worksheet)
        {
            OnImportingMessage("A importar facturas TMN");
            var dataSet = new TMNFacturaDataSet();
            var lojaDataSet = GetTmnShops();
            int rowIndex = FindNextTmnShop(worksheet, 1, lojaDataSet);
            while (rowIndex > 0)
            {
                rowIndex = LoadTmnShopBills(worksheet, rowIndex, lojaDataSet, dataSet);
                rowIndex = FindNextTmnShop(worksheet, rowIndex, lojaDataSet);
            }
            OnImportingMessage("");
            return dataSet;
        }

        private TMNFacturaDataSet LoadTmnBillsFromExcel(string fileName)
        {
            using (new UserInterfaceCulture())
            {
                var excelApplication = new Microsoft.Office.Interop.Excel.Application
                {
                    DisplayAlerts = false,
                    ScreenUpdating = false,
                    Visible = false,
                    UserControl = false,
                    Interactive = false,
                    DisplayInfoWindow = false,
                    DisplayStatusBar = false,
                    ShowStartupDialog = false
                };
                try
                {
                    excelApplication.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    if (excelApplication.Worksheets.Count == 0)
                        return new TMNFacturaDataSet();
                    var workSheet = (Worksheet)excelApplication.Worksheets[1];
                    if (workSheet == null)
                        return new TMNFacturaDataSet();
                    return LoadTmnBills(workSheet);
                }
                finally
                {
                    var workbooks = excelApplication.Workbooks;
                    workbooks.Close();
                    Marshal.ReleaseComObject(workbooks);
                    excelApplication.Quit();
                    Marshal.ReleaseComObject(excelApplication);
                    GC.Collect();
                }
            }
        }

        private int GetTmnShop(StreamReader reader, TMNLojasDataSet lojaDataSet)
        {
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                if (line == "" || !line.Contains("Loja"))
                    continue;
                foreach (TMNLojasDataSet.LojaRow shop in lojaDataSet.Loja)
                    if (line.Contains(shop.SAP.ToString()))
                        return shop.SAP;
                MessageBox.Show(OwnerWindow, string.Format("A '{0}' não está parametrizada.", line));
                return -1;
            }
            return -2;
        }

        private static void LoadTmnShopBills(int shop, StreamReader reader, TMNFacturaDataSet dataSet)
        {
            reader.ReadLine();
            reader.ReadLine();
            var line = reader.ReadLine();
            while (LineValid(line))
            {
                string[] values = line.Split(';');                
                TMNFacturaDataSet.FacturaRow bill = dataSet.Factura.NewFacturaRow();
                bill.SAPLoja = shop;
                bill.Empresa = values.Length < 1 ? "" : values[0];
                bill.Factura = values.Length < 2 ? "" : values[1];
                bill.Recibo = values.Length < 3 ? "" : values[2];
                bill.Conta = values.Length < 4 ? "" : values[3];
                bill.Valor = values.Length < 5 ? 0 : Convert.ToDouble(values[4]);
                if (values.Length >= 5 &&  !string.IsNullOrEmpty(values[5])) bill.DataHora = Convert.ToDateTime(values[5]);
                bill.Cheque = values.Length < 7 ? "" : values[6];
                bill.Banco = values.Length < 8 ? "" : values[7];
                dataSet.Factura.AddFacturaRow(bill);
                line = reader.ReadLine();
            }
        }

        private TMNFacturaDataSet LoadTmnBillsFromCsv(string fileName)
        {
            var lojaDataSet = GetTmnShops();
            var dataSet = new TMNFacturaDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            while (!reader.EndOfStream)
            {
                var shop = GetTmnShop(reader, lojaDataSet);
                if (shop == -2) return dataSet;
                if(shop == -1) return new TMNFacturaDataSet();
                OnImportingMessage("A importar facturas TMN da loja " + lojaDataSet.Loja.FindBySAP(shop).Nome);
                LoadTmnShopBills(shop, reader, dataSet);
            }
            return dataSet;
        }

        private static string GetPrinterName()
        {
            string printerName = ConfigurationManager.AppSettings["Impressora"];
            if (!string.IsNullOrEmpty(printerName))
                return printerName;
            var printManager = new PrintManager();
            return printManager.GetDefaultPrinterName();
        }

        private TMNImpressaoDataSet GetPrintTmnList(DateTime dateTime, int shop)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetPrintTmnList(sqlTransaction, dateTime, shop);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler a impressão da listagem TMN.");
                return new TMNImpressaoDataSet();
            }
        }

        private bool ExistsBills(DateTime day)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var exists = Database.ExistsTmnBills(sqlTransaction, day);
                sqlTransaction.Commit();
                return exists;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(),
                    string.Format("Não foi possível validar se existem facturas TMN no dia '{0}'.", day.ToShortDateString()));
                return true;
            }
        }

        private bool TmnBillsValid(TMNFacturaDataSet dataSet)
        {
            var importedDays = new List<DateTime>();
            foreach (TMNFacturaDataSet.FacturaRow bill in dataSet.Factura)
            {
                if (!bill.IsDataHoraNull() && !importedDays.Contains(bill.DataHora.Date))
                    importedDays.Add(bill.DataHora.Date);
            }
            if (importedDays.Count > 1)
            {
                MessageBox.Show(OwnerWindow, "A importação só suporta facturas de 1 dia.", "Importar Facturas TMN", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            var daysWithBills = "";
            foreach (var day in importedDays)
            {
                if (!ExistsBills(day))
                    continue;
                if (daysWithBills.Length > 0) daysWithBills += ", ";
                daysWithBills += day.ToShortDateString();
            }
            if (daysWithBills.Length > 0)
            {
                MessageBox.Show(OwnerWindow,
                                string.Format("Existem facturas nos dias {0}.", daysWithBills) + Environment.NewLine +
                                "Por favor, elimine as facturas.", "Importar Facturas TMN",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            foreach (TMNFacturaDataSet.FacturaRow bill in dataSet.Factura)
                if(bill.IsDataHoraNull())
                    bill.DataHora = importedDays[0];
            return true;
        }

        private bool SaveTmnBills(TMNFacturaDataSet dataSet)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                OnImportingMessage("A actualizar a base de dados com as facturas TMN importadas.");
                Database.InsertTmnBills(sqlTransaction, dataSet);                
                sqlTransaction.Commit();
                return true;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível gravar as facturas TMN.");
                return false;
            }
            finally
            {
                OnImportingMessage("");
            }
        }

        private void OnImportingMessage(string message)
        {
            EventHandler<ImportingArgs> eventHandler = ImportingMessage;
            if (eventHandler != null)
                eventHandler(this, new ImportingArgs(message));
        }

        private void ShowSuccessfulMessage()
        {
            MessageBox.Show(OwnerWindow, "Operação efectuada com sucesso", "Mobile Loader", MessageBoxButtons.OK);
        }


        public bool IsUserValid(string userName, string password)
        {
            var dataSource = ConfigurationManager.AppSettings["FonteDados"];
            var databaseName = ConfigurationManager.AppSettings["NomeBaseDados"];
            var connectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;", dataSource, databaseName, userName, password);
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Utilizador sem acesso à base de dados.");
                return false;
            }
            return true;
        }

        public void ImportTmnBills(string fileName)
        {
            TMNFacturaDataSet dataSet = Path.GetExtension(fileName) == ".csv" ? LoadTmnBillsFromCsv(fileName) : LoadTmnBillsFromExcel(fileName);
            if (dataSet.Factura.Count == 0)
                return;
            if (TmnBillsValid(dataSet) && SaveTmnBills(dataSet))
                ShowSuccessfulMessage();
        }

        public void DeleteTmnBills(DateTime dateTime)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                Database.DeleteTmnBills(sqlTransaction, dateTime);
                sqlTransaction.Commit();
                ShowSuccessfulMessage();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível eliminar as facturas TMN do dia pretendido.");
            }
        }

        public void ImportTmnLoads(string fileName)
        {
            TMNCarregamentoDataSet dataSet = Path.GetExtension(fileName) == ".csv" ? LoadTmnLoadsFromCsv(fileName) : LoadTmnLoadsFromExcel(fileName);
            if (dataSet.Carregamento.Count == 0)
                return;
            if (TmnLoadsValid(dataSet) && SaveTmnLoads(dataSet))
                ShowSuccessfulMessage();
        }

        public void DeleteTmnLoads(DateTime dateTime)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                Database.DeleteTmnLoads(sqlTransaction, dateTime);
                sqlTransaction.Commit();
                ShowSuccessfulMessage();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(),
                                                              "Não foi possível eliminar os carregamentos TMN do dia pretendido.");
            }
        }

        public TMNCarregamentoDataSet GetTmnLoads(ListFilter listFilter)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetTmnLoads(sqlTransaction, listFilter);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler a lista de carregamentos TMN.");
                return new TMNCarregamentoDataSet();
            }
        }

        public TMNFacturaDataSet GetTmnBills(ListFilter listFilter)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetTmnBills(sqlTransaction, listFilter);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler a lista de facturas TMN.");
                return new TMNFacturaDataSet();
            }
        }

        public TMNMovimentoDataSet GetTmnMovements(ListFilter listFilter)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetTmnMovements(sqlTransaction, listFilter);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler a lista de movimentos TMN.");
                return new TMNMovimentoDataSet();
            }
        }

        public TMNMovimentoDataSet GetTmnMovement(int shop, DateTime dateTime)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetTmnMovement(sqlTransaction, shop, dateTime);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler o movimento TMN.");
                return new TMNMovimentoDataSet();
            }
        }

        public TMNLojasDataSet GetShopsOfMovements()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetShopsOfTmnMovements(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler as lojas TMN.");
                return new TMNLojasDataSet();
            }
        }

        public TMNLojasDataSet GetShopsOfLoads()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetShopsOfTmnLoads(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler as lojas TMN.");
                return new TMNLojasDataSet();
            }
        }

        public TMNLojasDataSet GetShopsOfBills()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetShopsOfTmnBills(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler as lojas TMN.");
                return new TMNLojasDataSet();
            }
        }

        public TMNLojasDataSet GetShopsOfLoadsAndBills()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetShopsOfTmnLoadsAndBills(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler as lojas TMN.");
                return new TMNLojasDataSet();
            }
        }

        public void UpdateTmnMovement(TMNMovimentoDataSet dataSet)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                Database.UpdateTmnMovement(sqlTransaction, dataSet);
                sqlTransaction.Commit();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível actualizar o movimento TMN.");
            }
        }

        public event EventHandler<ImportingArgs> ImportingMessage;

        public TMNCarregamentoFacturaDataSet GetTmnDocuments(ListFilter listFilter)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetTmnDocuments(sqlTransaction, listFilter);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(OwnerWindow, e, LogFile, Product.MobileLoader.ToString(), "Não foi possível ler a lista de carregamentos e facturas TMN.");
                return new TMNCarregamentoFacturaDataSet();
            }
        }

        public void CloseConnections()
        {
            _connection.Close();
        }

        public void PrintTmnList(DateTime dateTime, int shop)
        {
            var dataSet = GetPrintTmnList(dateTime, shop);
            if (dataSet.Loja.Count == 0)
                return;
            var printManager = new PrintManager
            {
                NumberOfCopies = 1,
                PrinterName = GetPrinterName(),
                ReportFile = "ListagemTMN.rpt",
                DataSource = dataSet
            };
            printManager.Print();
        }

        public void PreviewTmnList(DateTime dateTime, int shop)
        {
            var form = new PrintPreviewXtraForm();
            form.Setup(GetPrintTmnList(dateTime, shop), "ListagemTMN.rpt");
            form.ShowDialog();
        }
    }
}
